package sparsing;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.RandomAccessFile;
import java.io.Writer;
import java.nio.ByteBuffer;
import java.nio.ByteOrder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class NetSparsing {

	// private final String DIRECTORY = "D:/els/net";
	private final String DB_USER = "root";
	private final String DB_PASSWORD = "root";
	private final String DB_SCHEMA = "net_street_sparsing";
	private final String URL = "jdbc:mysql://localhost:3306/" + DB_SCHEMA;
	// private final static int STEP = 100000;

	private int step;

	private int streetID;

	private int toDelete;
	private int typFehler;
	private int faktorFehler;
	private int beideFehler;

	public static void main(String[] args) throws FileNotFoundException,
			IOException, ClassNotFoundException {

		// int step = STEP;

		// NetSparsing sparsing = new NetSparsing(step);

		// sparsing.readStreetRecordsToDB();

		// System.gc();

		// sparsing.writeDegreesToDB();

		// System.gc();

		// sparsing.sparseGraph();

		// System.gc();

		// sparsing.exportStreetRecordsToCoreFile();

		// System.gc();

		// sparsing.createNodeMapping();

		// System.gc();

		// sparsing.createDDSG("D:\\sparseDACH_dur.ddsg");

		// System.gc();

		// sparsing.createAssignmentFile("D:\\assignmentDACH.ass");

		// int[] array = sparsing.readIDs();
		// ObjectOutputStream out = new ObjectOutputStream(new FileOutputStream(
		// "D:\\nodemapping.ser"));
		// out.writeObject(array);
		// out.flush();
		// out.close();
		//
		// array = null;
		// System.gc();
		//
		// ObjectInputStream in = new ObjectInputStream(new FileInputStream(
		// "D:\\nodemapping.ser"));
		// array = (int[]) in.readObject();
		// in.close();
		//
		// System.out.println("letzter Eintrag: " + array[array.length - 1]);

	}

	protected void createAssignmentFile(String fileName) {

		ObjectInputStream oIn = null;
		RandomAccessFile file = null;

		try {

			oIn = new ObjectInputStream(new FileInputStream(
					"D:\\nodemapping.ser"));
			int[] map = (int[]) oIn.readObject();

			file = new RandomAccessFile(fileName, "rw");

			Assignment[] assignments = new Assignment[step * 2];
			int i = 0;
			clear(assignments);
			getAssignments(assignments, i);
			while (assignments[0] != null) {

				for (int j = 0; j < assignments.length; j++) {
					if (assignments[j] == null)
						break;

					if (j % 2 == 0) {
						writeTypeAndFactor(file, assignments[j].getTyp(),
								assignments[j].getFactor());
					}
					writeAssignment(file, assignments[j], map);

				}

				if ((i + 1) % 10 == 0)
					System.out.println(((i + 1) * step) + " assignments read");

				clear(assignments);
				getAssignments(assignments, ++i);
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (oIn != null)
					oIn.close();
			} catch (Exception e) {
			}
			try {
				if (file != null)
					file.close();
			} catch (Exception e) {
			}
		}

	}

	private void writeAssignment(RandomAccessFile file, Assignment assignment,
			int[] map) {
		try {

			ByteBuffer buffer = ByteBuffer.allocate(4);
			buffer.order(ByteOrder.LITTLE_ENDIAN);
			buffer.putInt(assignment.getDeletedID());

			file.write(buffer.array());

			buffer.clear();
			buffer.putInt(map[assignment.getAssignedID()]);

			file.write(buffer.array());

			int km = new Double((assignment.getKm() * 1000) + 0.5).intValue();
			buffer.clear();
			buffer.putInt(km);

			file.write(buffer.array());

			buffer = ByteBuffer.allocate(2);
			buffer.order(ByteOrder.BIG_ENDIAN);
			buffer.putShort(assignment.getDirection());

			file.write(buffer.array());

		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private void writeTypeAndFactor(RandomAccessFile file, short typ,
			short factor) {
		try {

			ByteBuffer buffer = ByteBuffer.allocate(2);
			buffer.order(ByteOrder.BIG_ENDIAN);
			buffer.putShort(typ);

			file.write(buffer.array());

			buffer.clear();
			buffer.putShort(factor);

			file.write(buffer.array());

		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private void getAssignments(Assignment[] assignments, int i) {

		String sql = new String();
		sql += "SELECT * FROM assignment WHERE DeletedID > " + (i * step)
				+ " AND DeletedID <= " + ((i + 1) * step)
				+ " ORDER BY DeletedID";

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());

			i = 0;
			while (rs.next()) {

				Assignment ass = new Assignment();
				ass.setID(rs.getInt("ID"));
				ass.setDeletedID(rs.getInt("DeletedID"));
				ass.setAssignedID(rs.getInt("AssignedID"));
				ass.setKm(rs.getDouble("Km"));
				ass.setDirection(rs.getShort("Richtung"));
				ass.setFactor(rs.getShort("FaktorPKW"));
				ass.setTyp(rs.getShort("RoadClass"));
				assignments[i++] = ass;

			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
	}

	public void createDDSG(String fileName) {

		Writer output = null;

		try {

			File textFile = new File(fileName);
			if (!textFile.exists())
				textFile.createNewFile();

			output = new BufferedWriter(new OutputStreamWriter(
					new FileOutputStream(textFile)));

			output.write("d\n");

			output.flush();

			int[] orgToNew = readIDs();

			System.gc();

			Street[] streets = new Street[step];
			clear(streets);

			int i = 0;
			getStreets(streets, i);
			while (streets[0] != null) {

				for (int j = 0; j < streets.length; j++) {
					if (streets[j] == null) {
						break;
					}

					Street streetRecord = streets[j];

					int source = orgToNew[streetRecord.getFromID()];
					int target = orgToNew[streetRecord.getToID()];
					int weight = new Double((streetRecord.getKm() * 1000) + 0.5)
							.intValue();
					short direction = streetRecord.getDirection();
					short type = streetRecord.getTyp();
					short factor = streetRecord.getFactor();

					assert (source >= 0 && target >= 0) : "Da stimmt was mit den IDs nicht (orgIDs: "
							+ streetRecord.getFromID()
							+ ", "
							+ streetRecord.getToID() + ")";

					output.write(source + " " + target + " " + weight + " "
							+ direction + " " + type + " " + factor + "\n");

				}

				output.flush();

				if ((i + 1) % 10 == 0)
					System.out.println(((i + 1) * step) + " nodes read");

				clear(streets);
				getStreets(streets, ++i);
			}

		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (output != null) {
					output.close();
				}
			} catch (Exception e) {
			}
		}
	}

	private int[] readIDs() {

		int step = 100000;

		int length = getIDLength();
		int[] res = new int[length];

		for (int i = 0; i < res.length; i++) {
			res[i] = -1;
		}

		int i = 0;
		while (readNodes(res, i, step)) {
			i++;
		}

		return res;
	}

	private boolean readNodes(int[] nodes, int i, int step) {

		String sql = new String();
		sql += "SELECT * FROM nodemapping WHERE orgID > " + (i * step)
				+ " AND orgID <= " + ((i + 1) * step);

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		boolean res = false;

		try {

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/net_street_sparsing", "root",
					"root");
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());

			while (rs.next()) {
				nodes[rs.getInt("orgID")] = rs.getInt("newID");
				res = true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
		return res;
	}

	private int getIDLength() {

		String sql = new String();
		sql += "SELECT MAX(orgID) FROM nodemapping";

		int res = 0;

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/net_street_sparsing", "root",
					"root");
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());

			if (rs.next()) {
				res = rs.getInt("MAX(orgID)");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
		return ++res;
	}

	public void createNodeMapping() {

		truncateTable("cross_degree");
		truncateTable("nodemapping");
		writeDegreesToDB();

		int maxNode = getMaxNodeID();
		int[] newToOrg = new int[maxNode];

		int id = 0;
		int[] nodes = new int[step];

		int i = 0;
		clear(nodes);
		getNodes(nodes, i);
		while (nodes[0] >= 0) {

			for (int j = 0; j < nodes.length; j++) {
				if (nodes[j] < 0)
					break;

				newToOrg[id++] = nodes[j];

			}

			if ((i + 1) % 10 == 0)
				System.out.println(((i + 1) * step) + " nodes read");

			clear(nodes);
			getNodes(nodes, ++i);
		}

		i = 0;
		clear(nodes);
		getNodes2(nodes, i);
		while (nodes[0] >= 0) {

			for (int j = 0; j < nodes.length; j++) {
				if (nodes[j] < 0)
					break;

				newToOrg[id++] = nodes[j];

			}

			if ((i + 1) % 10 == 0)
				System.out.println(((i + 1) * step) + " nodes read");

			clear(nodes);
			getNodes2(nodes, ++i);
		}

		for (; id < newToOrg.length; id++) {
			newToOrg[id] = -1;
		}

		mapIDs(newToOrg);

	}

	private int getMaxNodeID() {

		String sql = new String();
		sql += "SELECT MAX(VonIntnr) FROM street_record";

		int res = 0;

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());

			int node1 = 0;
			int node2 = 0;
			int node3 = 0;

			if (rs.next()) {
				node1 = rs.getInt("MAX(VonIntnr)");
			}

			sql = "SELECT MAX(BisIntnr) FROM street_record";
			rs = con.createStatement().executeQuery(sql.toString());
			if (rs.next()) {
				node2 = rs.getInt("MAX(BisIntnr)");
			}

			res = Math.max(node1, node2);

			sql = "SELECT MAX(deletedID) FROM assignment";
			rs = con.createStatement().executeQuery(sql.toString());
			if (rs.next()) {
				node3 = rs.getInt("MAX(deletedID)");
			}

			res = Math.max(res, node3) + 1;

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
		return res;
	}

	private void mapIDs(int[] newToOrg) {

		System.out.println("insert mapping ......");

		StringBuilder sql = new StringBuilder();

		Connection con = null;
		Statement stmt = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);

			String sqlHead = "INSERT INTO nodemapping (orgID,newID) VALUES ";

			sql.append(sqlHead);

			System.out.print("%" + "        " + "10" + "        " + "20"
					+ "        " + "30" + "        " + "40" + "        " + "50"
					+ "        " + "60" + "        " + "70" + "        " + "80"
					+ "        " + "90" + "      " + "100\n");
			int percent = 0;

			int i = 1;
			int length = newToOrg.length;
			for (int id = 0; id < newToOrg.length; id++) {

				if (newToOrg[id] < 0) {
					break;
				}

				sql.append("(");
				sql.append(newToOrg[id]);
				sql.append(",");
				sql.append(id);
				sql.append("),");

				if (i % step == 0) {

					sql.deleteCharAt(sql.length() - 1);
					stmt = con.createStatement();
					stmt.executeUpdate(sql.toString());

					stmt.close();

					sql = new StringBuilder();
					sql.append(sqlHead);
				}

				double percentTmp = ((i) / (double) length) * 100;
				for (; percent < percentTmp; percent++) {
					System.out.print("-");
				}

				i++;
			}

			if (i % step != 1) {
				sql.deleteCharAt(sql.length() - 1);
				stmt = con.createStatement();
				stmt.executeUpdate(sql.toString());
				stmt.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
	}

	private void getNodes(int[] nodes, int i) {

		String sql = new String();
		sql += "SELECT Intnr FROM cross_degree WHERE Intnr > " + (i * step)
				+ " AND Intnr <= " + ((i + 1) * step);

		Connection con = null;
		Statement stmt = null;
		ResultSet res = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);
			stmt = con.createStatement();
			res = stmt.executeQuery(sql.toString());

			i = 0;
			while (res.next()) {
				nodes[i++] = res.getInt("Intnr");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (res != null)
					res.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
	}

	private void getNodes2(int[] nodes, int i) {

		String sql = new String();
		sql += "SELECT DISTINCT deletedID FROM assignment WHERE deletedID > "
				+ (i * step) + " AND deletedID <= " + ((i + 1) * step);

		Connection con = null;
		Statement stmt = null;
		ResultSet res = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);
			stmt = con.createStatement();
			res = stmt.executeQuery(sql.toString());

			i = 0;
			while (res.next()) {
				nodes[i++] = res.getInt("deletedID");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (res != null)
					res.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
	}

	private void truncateTable(String table) {

		String sql = new String();
		sql += "TRUNCATE TABLE " + table;

		Connection con = null;
		Statement stmt = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);
			stmt = con.createStatement();
			stmt.executeUpdate(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
	}

	public NetSparsing(int step) {

		streetID = 0;

		toDelete = 0;
		typFehler = 0;
		faktorFehler = 0;
		beideFehler = 0;

		this.step = step;

	}

	public void sparseGraph() {

		try {

			System.out.println("sparse Graph ......");

			setStreetID();

			Street[] streets = new Street[2];
			int[] nodes = new int[step];

			int i = 0;
			clear(nodes);
			getNodesToDelete(nodes, i);
			while (nodes[0] >= 0) {

				Connection con = DriverManager.getConnection(URL, DB_USER,
						DB_PASSWORD);

				for (int j = 0; j < nodes.length; j++) {
					if (nodes[j] < 0)
						break;

					checkNode(con, nodes[j], streets);
				}

				con.close();

				// if ((i + 1) % 10 == 0)
				System.out.println(((i + 1) * step) + " nodes read");

				clear(nodes);
				getNodesToDelete(nodes, ++i);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		System.out.println(toDelete + " toDelete");
		System.out.println(typFehler + " typFehler");
		System.out.println(faktorFehler + " faktorFehler");
		System.out.println(beideFehler + " beideFehler");

	}

	private void setStreetID() {

		String sql = new String();
		sql += "SELECT MAX(Intnr) FROM street_record";

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());
			if (rs.next()) {
				streetID = rs.getInt("MAX(Intnr)") + 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}

	}

	private static void clear(int[] nodes) {
		for (int i = 0; i < nodes.length; i++) {
			nodes[i] = -1;
		}
	}

	private void checkNode(Connection con, int node, Street[] streets) {

		Street.getStreets(con, node, streets);

		if (streets[0].getTyp() == streets[1].getTyp()) {
			if (streets[0].getFactor() == streets[1].getFactor()) {
				toDelete++;
				deleteNode(con, node, streets);
			} else {
				faktorFehler++;
			}
		} else {
			if (streets[0].getFactor() == streets[1].getFactor()) {
				typFehler++;
			} else {
				beideFehler++;
			}
		}

	}

	private void deleteNode(Connection con, int node, Street[] streets) {

		int assignNode1 = streets[0].getFromID() == node ? streets[0].getToID()
				: streets[0].getFromID();
		int assignNode2 = streets[1].getFromID() == node ? streets[1].getToID()
				: streets[1].getFromID();

		short dir1 = 0;
		short dir2 = 0;

		if (!streets[0].bidir()) {
			if (streets[0].outgoing(node)) {
				dir1 = 1;
			} else {
				dir1 = 2;
			}
		}

		if (!streets[1].bidir()) {
			if (streets[1].outgoing(node)) {
				dir2 = 1;
			} else {
				dir2 = 2;
			}
		}

		Assignment assignment1 = new Assignment(0, node, assignNode1,
				streets[0].getKm(), dir1, streets[0].getFactor(),
				streets[0].getTyp());
		Assignment assignment2 = new Assignment(0, node, assignNode2,
				streets[1].getKm(), dir2, streets[1].getFactor(),
				streets[1].getTyp());

		Street street = new Street();
		street.setId(streetID++);
		street.setFromID(assignNode1);
		street.setToID(assignNode2);
		street.setKm(streets[0].getKm() + streets[1].getKm());
		street.setFactor(assignment1.getFactor());
		street.setTyp(assignment1.getTyp());

		if (streets[0].bidir() && streets[1].bidir()) {

			street.setDirection((short) 0);

		} else if (streets[0].bidir()) {

			if (streets[1].outgoing(node)) {
				street.setDirection((short) 1);
			} else {
				street.setDirection((short) 2);
			}

		} else if (streets[1].bidir()) {

			if (streets[0].outgoing(node)) {
				street.setDirection((short) 2);
			} else {
				street.setDirection((short) 1);
			}

		} else {

			if (streets[1].outgoing(node)) {
				street.setDirection((short) 1);
				assert (!streets[0].outgoing(node)) : "beide Kanten sind outgoing!!!";
			} else {
				street.setDirection((short) 2);
				assert (streets[0].outgoing(node)) : "beide Kanten sind ingoing!!!";
			}

		}

		street.insertStreet(con);
		Assignment.insertAssignments(con, assignment1, assignment2);

		changeAssignments(con, node, assignNode1, assignNode2, streets[0],
				streets[1]);

		Street.deleteStreets(con, streets[0], streets[1]);

	}

	private void changeAssignments(Connection con, int node, int assignNode1,
			int assignNode2, Street s1, Street s2) {

		List<Assignment> assignments = Assignment.getAllAssignments(con, node);
		for (Assignment assignment : assignments) {

			Assignment coAssignment = assignment.getCoAssignment(con);

			if (coAssignment.getAssignedID() == assignNode1) {

				assignment.setAssignedID(assignNode2);
				assignment.setKm(s2.getKm() + assignment.getKm());

				if (assignment.getDirection() == 0 && !s2.bidir()) {

					assignment
							.setDirection((short) (s2.outgoing(node) ? 1 : 2));

				}

			} else {

				assignment.setAssignedID(assignNode1);
				assignment.setKm(s1.getKm() + assignment.getKm());

				if (assignment.getDirection() == 0 && !s1.bidir()) {

					assignment
							.setDirection((short) (s1.outgoing(node) ? 1 : 2));

				}

			}

			assignment.updateAssignment(con);

		}

	}

	private void getNodesToDelete(int[] nodes, int i) {

		String sql = new String();
		sql += "SELECT Intnr FROM cross_degree WHERE Degree = 2 AND Intnr > "
				+ (i * step) + " AND Intnr <= " + ((i + 1) * step);

		Connection con = null;
		Statement stmt = null;
		ResultSet res = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);
			stmt = con.createStatement();
			res = stmt.executeQuery(sql.toString());

			i = 0;
			while (res.next()) {
				nodes[i++] = res.getInt("Intnr");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (res != null)
					res.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
	}

	public void writeDegreesToDB() {

		int noOfNodes = getNoOfNodes();

		int[] idToDegree = new int[noOfNodes];

		System.out.println("read street records ......");
		System.out.println(noOfNodes + " nodes");

		Street[] records = new Street[step];

		int i = 0;
		getStreets(records, i);
		while (records[0] != null) {

			for (int j = 0; j < records.length; j++) {

				if (records[j] == null)
					break;

				increaseDegrees(idToDegree, records[j].getFromID(),
						records[j].getToID());

			}

			if ((i + 1) % 10 == 0)
				System.out.println(((i + 1) * step) + " records read");

			clear(records);
			getStreets(records, ++i);
		}

		System.out.println();

		writeDegrees(idToDegree);

	}

	private static void clear(Object[] records) {
		for (int i = 0; i < records.length; i++) {
			records[i] = null;
		}
	}

	private int getNoOfNodes() {

		String sql1 = new String();
		sql1 += "SELECT MAX(VonIntnr) FROM street_record";

		int res = 0;

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql1.toString());

			int node1 = 0;
			int node2 = 0;
			if (rs.next()) {
				node1 = rs.getInt("MAX(VonIntnr)");
			}
			sql1 = "SELECT MAX(BisIntnr) FROM street_record";
			rs = con.createStatement().executeQuery(sql1.toString());
			if (rs.next()) {
				node2 = rs.getInt("MAX(BisIntnr)");
			}
			res = Math.max(node1, node2) + 1;

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql1);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
		return res;
	}

	private void writeDegrees(int[] idToDegree) {

		System.out.println("insert degree data in database ......");

		StringBuilder sql = new StringBuilder();

		Connection con = null;
		Statement stmt = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);

			String sqlHead = "INSERT INTO cross_degree (Intnr,Degree) VALUES ";

			sql.append(sqlHead);

			System.out.print("%" + "        " + "10" + "        " + "20"
					+ "        " + "30" + "        " + "40" + "        " + "50"
					+ "        " + "60" + "        " + "70" + "        " + "80"
					+ "        " + "90" + "      " + "100\n");
			int percent = 0;

			int i = 1;
			int length = idToDegree.length;
			for (int key = 0; key < idToDegree.length; key++) {

				if (idToDegree[key] < 1) {
					continue;
				}

				sql.append("(");
				sql.append(key);
				sql.append(",");
				sql.append(idToDegree[key]);
				sql.append("),");

				if (i % step == 0) {

					sql.deleteCharAt(sql.length() - 1);
					stmt = con.createStatement();
					stmt.executeUpdate(sql.toString());

					stmt.close();

					sql = new StringBuilder();
					sql.append(sqlHead);
				}

				double percentTmp = ((i) / (double) length) * 100;
				for (; percent < percentTmp; percent++) {
					System.out.print("-");
				}

				i++;
			}

			if (i % step != 1) {
				sql.deleteCharAt(sql.length() - 1);
				stmt = con.createStatement();
				stmt.executeUpdate(sql.toString());
				stmt.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}

	}

	private static void increaseDegrees(int[] idToDegree, int fromID, int toID) {

		idToDegree[fromID] = idToDegree[fromID] + 1;
		idToDegree[toID] = idToDegree[toID] + 1;

	}

	private void getStreets(Street[] records, int i) {

		String sql = new String();
		sql += "SELECT * FROM street_record WHERE Intnr > " + (i * step)
				+ " AND Intnr <= " + ((i + 1) * step);

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {

			con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());

			i = 0;
			while (rs.next()) {

				Street street = new Street();
				street.setId(rs.getInt("Intnr"));
				street.setFromID(rs.getInt("VonIntnr"));
				street.setToID(rs.getInt("BisIntnr"));
				street.setKm(rs.getDouble("Km"));
				street.setDirection(rs.getShort("Richtung"));
				street.setFactor(rs.getShort("FaktorPKW"));
				street.setTyp(rs.getShort("RoadClass"));
				records[i++] = street;

			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
				pw.append(sql);
				pw.flush();
				pw.close();
			} catch (FileNotFoundException e1) {
				System.err.println("Could not write erroneous-sql.txt");
			}
			System.exit(-1);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			try {
				if (con != null)
					con.close();
			} catch (Exception e) {
			}
		}
	}
	// <-- =============================================================== -->
	// uncomment the following method to import all street records with the
	// "PlantourStreetCrossHelper"
	// <-- =============================================================== -->

	// public void readStreetRecordsToDB() {
	//
	// // Read street record from core file (file must exist)
	// StreetWorker streetWorker = new StreetWorker(DIRECTORY);
	// ObjectExporter<StreetRecord> exporter = new DBStreetRecordExporter(URL,
	// DB_USER, DB_PASSWORD);
	// streetWorker.setExporter(exporter);
	// streetWorker.importFiles(0, 0, 0);
	//
	// }

	// public void exportStreetRecordsToCoreFile() {
	//
	// System.out.println("export street records to core file......");
	//
	// StreetWorker streetWorker = new StreetWorker("D:\\net_export");
	//
	// StreetRecord[] records = new StreetRecord[step];
	//
	// int i = 1;
	// getStreetRecords(records, i);
	// while (records[0] != null) {
	//
	// for (int j = 0; j < records.length; j++) {
	//
	// if (records[j] == null)
	// break;
	//
	// try {
	// streetWorker.exportToCoreFile(records[j], false);
	// } catch (IOException e) {
	// e.printStackTrace();
	// } catch (StructException e) {
	// e.printStackTrace();
	// }
	//
	// }
	//
	// if (i % 10 == 0)
	// System.out.println((i * step) + " records read");
	//
	// clear(records);
	// getStreetRecords(records, ++i);
	// }
	//
	// try {
	// streetWorker.exportToCoreFile(null, true);
	// } catch (IOException e) {
	// e.printStackTrace();
	// } catch (StructException e) {
	// e.printStackTrace();
	// }
	// }

	// private void clear(StreetRecord[] records) {
	//
	// for (int i = 0; i < records.length; i++) {
	// records[i] = null;
	// }
	//
	// }

	// private void getStreetRecords(StreetRecord[] records, int i) {
	//
	// String sql = new String();
	// sql += "SELECT * FROM street_record WHERE Intnr <= " + (i * step)
	// + " AND Intnr > " + ((i - 1) * step);
	//
	// Connection con = null;
	// Statement stmt = null;
	// ResultSet rs = null;
	//
	// try {
	//
	// con = DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);
	// stmt = con.createStatement();
	// rs = stmt.executeQuery(sql.toString());
	//
	// i = 0;
	// while (rs.next()) {
	//
	// StreetRecord street = new StreetRecord();
	//
	// street.field_eIntnr = rs.getInt("Intnr");
	// street.field_eKarte = 0; // int
	// street.field_ePlz = ""; // String
	// street.field_eOrt = ""; // String
	// street.field_eTeilort = ""; // String
	// street.field_eGebiet = ""; // String
	// street.field_eStrassenName = ""; // String
	// street.field_eHausNrStruktur = 0; // int
	// street.field_eErsteHausNrLinks = ""; // String
	// street.field_eErsteHausNrRechts = ""; // String
	// street.field_eMiHausNrLinks = ""; // String
	// street.field_eMiHausNrRechts = ""; // String
	// street.field_eLetzeHausNrLinks = ""; // String
	// street.field_eLetzeHausNrRechts = ""; // String
	// street.field_eStr = ""; // String
	// street.field_eBez = rs.getShort("RoadClass"); // int
	// street.field_eQuadrat = 0; // short
	// street.field_eRoadClass = street.field_eBez; // short
	// street.field_eKm = rs.getDouble("Km"); // double
	// street.field_eFaktorPkw = rs.getShort("FaktorPkw"); // short
	// street.field_eFaktorLkw = street.field_eFaktorPkw; // short
	// street.field_eMaxHoehe = 0; // double
	// street.field_eMaxBreite = 0; // double
	// street.field_eMaxGewicht = 0; // double
	// street.field_eGefGut = 0; // int
	// street.field_eTLimit = 0; // int
	// street.field_eRichtung = rs.getShort("Richtung"); // int
	// street.field_eWetter = 0; // int
	// street.field_eAktiv = "1"; // String
	// street.field_eDetail = street.field_eBez; // int
	// street.field_eZeit = rs.getShort("Zeit"); // int
	// street.field_eIndividuell = 0; // short
	// street.field_eVorgaenger = 0; // long
	// street.field_eNr = 0; // int
	// street.field_eNachfolger = 0; // long
	// street.field_eLfdNr = 0; // int
	// street.field_eVonIntnr = rs.getInt("VonIntnr"); // long
	// street.field_eBisIntnr = rs.getInt("BisIntnr"); // long
	// street.field_eVonLkz = 49; // int
	// street.field_eBisLkz = 49; // int
	// street.field_ePermanentId = ""; // String
	// street.field_eQuadrant = 0; // short
	// street.field_ePlzId = 0; // long
	// street.field_esource = 0;
	//
	// records[i++] = street;
	//
	// }
	//
	// } catch (SQLException e) {
	// e.printStackTrace();
	// try {
	// PrintWriter pw = new PrintWriter("D:\\erroneous-sql.txt");
	// pw.append(sql);
	// pw.flush();
	// pw.close();
	// } catch (FileNotFoundException e1) {
	// System.err.println("Could not write erroneous-sql.txt");
	// }
	// System.exit(-1);
	// } finally {
	// try {
	// if (rs != null)
	// rs.close();
	// } catch (Exception e) {
	// }
	// try {
	// if (stmt != null)
	// stmt.close();
	// } catch (Exception e) {
	// }
	// try {
	// if (con != null)
	// con.close();
	// } catch (Exception e) {
	// }
	// }
	// }

}
